package jhta;

import java.awt.BorderLayout;
import java.awt.Image;
import java.awt.Toolkit;

import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JInternalFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.JTableHeader;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;
import javax.swing.JLayeredPane;
import javax.swing.border.TitledBorder;
import javax.swing.UIManager;

import java.awt.Color;

import javax.swing.SwingConstants;
import javax.swing.JFormattedTextField;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.Format;
import java.util.Vector;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.awt.event.KeyAdapter;
import java.awt.event.KeyEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.ItemListener;
import java.awt.event.ItemEvent;
import javax.swing.JTextArea;
import java.awt.Font;

public class CodeUpdate extends JInternalFrame {
	DefaultTableModel model;
	MyTableRender render;
	TableRowSorter<TableModel> tSoter;
	JavaDBConnection jdb = new JavaDBConnection();
	Vector<String> vc;
    
	
	
	int serial=0;
	private JPanel panel_1;
	private JScrollPane scrollPane;
	private JTable table;
	private JLayeredPane layeredPane;
	private JComboBox combo;
	private JFormattedTextField word;
	private JButton search;
	private JLayeredPane layeredPane_1;
	private JLabel label_1;
	private JLabel label_2;
	private JFormattedTextField minor;
	private JFormattedTextField major;
	private JButton modi;
	private JButton del;
	private JScrollPane scrollPane_1;
	private JTextArea textArea;
	private JLabel ttt;


	public CodeUpdate() {
		super("도서분류 코드 조회 및 수정", true, true, true, true);
		setFont(new Font("맑은 고딕", Font.PLAIN, 12));
		setDefaultCloseOperation(JInternalFrame.HIDE_ON_CLOSE);
		setBounds(750,100,736,513);
		getContentPane().setLayout(new BorderLayout(0, 0));
		getContentPane().add(getPanel_1(), BorderLayout.CENTER);


		JTableHeader jh1 = table.getTableHeader();

		jh1.setReorderingAllowed(false);//컬럼 이동 금지

		setResizable(false);

	}
	public void searchAll(){ //전체 조회

		model.setRowCount(0); //초기화
		String sql="";
		String wor = word.getText().trim();
		String comb = (String)combo.getSelectedItem();

		if(wor.equals("")){//검색어가 없을때
			sql = "select code_big, code_small, code_name from code ";

		}else{//검색어가 있을때
			sql = "select code_big, code_small, code_name "
					+ "from code where "
					+ " (code_big like '%"+wor+"%' or code_small like '%"+wor+"%' or code_name like '%"+wor+"%') ";
		}

		try{
			PreparedStatement pstmt = jdb.getConn().prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			ResultSetMetaData rsmd  = rs.getMetaData(); // 데이타(테이블) 의 구조

			while(rs.next()){	
				Vector vc = new Vector<>(); // dbms에서 가져온 값을 담을 백터.
				for (int i = 1; i <= rsmd.getColumnCount(); i++) { // 컬럼의 길이까지
					vc.add(rs.getString(i)); // 1,2,3, i 번째 컬럼의 데이터값 가져오기

				}
				model.addRow(vc); // 로우 추가
			}
			table.setModel(model);
			rs.close();
			pstmt.close();

		}catch(Exception ex){
			ex.printStackTrace();
		}


	}
	public void search(){ //콤보박스 선택 분류

		model.setRowCount(0); //초기화 

		String code = word.getText().trim();
		String comb = (String)combo.getSelectedItem();


		String sql="";
		if(code.equals("")){ //콤보박스 선택된 것 전체검색
			sql = "select code_big, code_small,code_name from "
					+ "code where code_big =(select code_big from code where code_name = '"+comb+"' and code_small is null) ";//도서 전체조회
		}else{
			//콤보분류 에서 검색어를 통해서 검색한 결과
			sql = "select code_big, code_small,code_name from code where code_big =(select code_big from code where code_name = '"+comb+"' and code_small is null)"
					+ " and (code_big like '%"+code+"%' or code_small like '%"+code+"%' or code_name like '%"+code+"%') ";
		}

		try{
			PreparedStatement pstmt = jdb.getConn().prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			ResultSetMetaData rsmd  = rs.getMetaData(); // 데이타(테이블) 의 구조

			while(rs.next()){	
				Vector vc = new Vector<>(); // dbms에서 가져온 값을 담을 백터.
				for (int i = 1; i <= rsmd.getColumnCount(); i++) { // 컬럼의 길이까지
					vc.add(rs.getString(i)); // 1,2,3, i 번째 컬럼의 데이터값 가져오기

				}
				model.addRow(vc); // 로우 추가
			}
			table.setModel(model);
			rs.close();
			pstmt.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}

	}


	public void click(){
		major.setEditable(true);
		major.setText("");
		minor.setText("");

		int row = table.getSelectedRow(); //클릭하여 가져온 행
		String big =(String)table.getValueAt(row, 0); // 가져온 행의 1번째 칼럼
		String small =(String)table.getValueAt(row, 1); // 가져온 행의 2번째 칼럼



		//소분류명 뿌려주기
		String sql ="select code_name from code where code_small = '"+small+"' ";
		try{

			PreparedStatement pstmt = jdb.getConn().prepareStatement(sql);

			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				String mi=rs.getString(1);
				minor.setText(mi);   
			}
			rs.close();
			pstmt.close();

		}catch(Exception ex){
			ex.printStackTrace();
		}
		//대분류명 뿌려주기
		String sql2 = "select code_name from code where code_big = '"+big+"' and code_small is null ";
		try{

			PreparedStatement pstmt = jdb.getConn().prepareStatement(sql2);

			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){

				String ma=rs.getString(1);
				major.setText(ma);
				if(big.equals("A1")||big.equals("A2")||big.equals("A3")){
					major.setEditable(false);
				}
			}
			rs.close();
			pstmt.close();

		}catch(Exception ex){
			ex.printStackTrace();
		}
		//해당 데이터 시리얼번호 저장
		String sql3;
		if(minor.getText().equals("")){
			sql3 ="select code_no from code where code_big='"+big+"' and code_small is null ";	  
		}else{
			sql3 = "select code_no from code where code_big ='"+big+"' and code_small = '"+small+"' ";

		}
		try{
			PreparedStatement pstmt = jdb.getConn().prepareStatement(sql3);

			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				serial=rs.getInt(1);

			}
			rs.close();
			pstmt.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public void modify(){ //수정
		
		String ma = major.getText().trim(); //대분류 가져온 값
		String mi = minor.getText().trim(); //소분류 가져온 값
		
		
		if(!ma.equals("")|| !mi.equals("")){
			
			String sql ="update code set code_name = '"+mi+"' where code_no = "+serial+" ";
			
			
			
			try{
				PreparedStatement pstmt = jdb.getConn().prepareStatement(sql);
				pstmt.execute();
				pstmt.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
			String sql2 ="update code set code_name = '"+ma+"' "
					+ "where code_big = (select code_big from code where code_no ="+serial+") and code_small is null ";
			
			try{
				PreparedStatement pstmt = jdb.getConn().prepareStatement(sql2);
				pstmt.execute();
				pstmt.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
			if(combo.getSelectedIndex() ==0){
				searchAll(); //전체조회
			}else{
				search();
			}
			
			major.setText("");
			minor.setText("");
			JOptionPane.showMessageDialog(null, "대분류 : "+ma+" , 소분류: "+mi+" 으로 수정 되었습니다.");		
		
		}else{
			JOptionPane.showMessageDialog(null, "조회된 데이터가 없습니다.");
			
		}
		



	}

	public void delete(){ //삭제


		String ma = major.getText().trim(); //대분류 가져온 값
		String mi = minor.getText().trim(); //소분류 가져온 값

        if(!ma.equals("")|| !mi.equals("")){
        	
        	int k = JOptionPane.showConfirmDialog(null, "삭제시 해당 기존 데이터가 손상될수 있습니다. 정말 삭제 하시겠습니까?","삭제창",JOptionPane.YES_NO_OPTION);
        	
        	if(k==0){//
        	String sql = "delete from code where code_no ="+serial+" ";
        	
        	try{
        		PreparedStatement pstmt = jdb.getConn().prepareStatement(sql);
        		pstmt.execute();
        		pstmt.close();
        		
        	}catch(Exception ex){
        		ex.printStackTrace();
        	}
        		
        		JOptionPane.showMessageDialog(null, "대분류 : "+ma+" , 소분류: "+mi+" 가 삭제가 완료되었습니다.");		
        	}
        	
        	major.setText("");
        	minor.setText("");
        	
        	if(combo.getSelectedIndex() ==0){
        		searchAll(); //전체조회
        	}else{
        		search();
        	}
        	
        }else{
        	JOptionPane.showMessageDialog(null, "조회된 데이터가 없습니다.");
        }
		



	}
	private JPanel getPanel_1() {
		if (panel_1 == null) {
			panel_1 = new JPanel();
			panel_1.setLayout(null);
			panel_1.add(getScrollPane());
			panel_1.add(getLayeredPane_1());
			panel_1.add(getLayeredPane_1_1());
			panel_1.add(getScrollPane_1());
			panel_1.add(getTtt());
		}
		return panel_1;
	}
	private JScrollPane getScrollPane() {
		if (scrollPane == null) {
			scrollPane = new JScrollPane();
			scrollPane.setBounds(12, 81, 408, 392);
			scrollPane.setViewportView(getTable());
		}
		return scrollPane;
	}
	private JTable getTable() {
		if (table == null) {
			String[] t = {"대분류","소분류","분류명"};
			//model = new DefaultTableModel(t, 0);
			table = new JTable();
			model = new DefaultTableModel(t, 0) {
				// 셀 내용 수정 불가
				public boolean isCellEditable(int rowIndex, int mColIndex) {
					return false;
				}
			};
			
			
			table.addMouseListener(new MouseAdapter() {
				@Override
				public void mouseReleased(MouseEvent arg0) {
					click();


				}
			});
			table.setModel(model);
			tSoter = new TableRowSorter(model);
			table.setRowSorter(tSoter);

			char[] c={'c','c','c'};
			int[] w={120,120,120};
			render = new MyTableRender(table, c,w);
		//	model.addRow(t);

		}
		return table;
	}
	private JLayeredPane getLayeredPane_1() {
		if (layeredPane == null) {
			layeredPane = new JLayeredPane();
			layeredPane.setFont(new Font("맑은 고딕", Font.PLAIN, 12));
			layeredPane.setBorder(new TitledBorder(UIManager.getBorder("TitledBorder.border"), "\uCF54\uB4DC\uC870\uD68C", TitledBorder.LEADING, TitledBorder.TOP, null, new Color(0, 0, 0)));
			layeredPane.setBounds(12, 10, 408, 61);
			layeredPane.add(getComboBox_1());
			layeredPane.add(getWord());
			layeredPane.add(getSearch());
		}
		return layeredPane;
	}
	private JComboBox getComboBox_1() {
		if (combo == null) {
			//String[] t = {"전체","도서분류","회원분류"};
			//콤보박스 대분류 분류명만 뽑기
			try{
				String sql2="select code_name from code where code_small is null order by code_big  ";//대분류에 있는 글자만 뽑	

				PreparedStatement pstmt = jdb.getConn().prepareStatement(sql2);
				ResultSet rs = pstmt.executeQuery();//담아올래
				Vector<String> vc = new Vector<String>();
				vc.add("전체");
				while(rs.next()){

					vc.add(rs.getString(1)); //대분류 콤보박스에 전체가 들어갑니다.

				}
				//백터 콤보에 뿌리
				combo = new JComboBox(vc);
				combo.addItemListener(new ItemListener() {
					public void itemStateChanged(ItemEvent arg0) {
						if(combo.getSelectedIndex() ==0){
							searchAll(); //전체조회
						}else{

							search(); //콤보박스 선택관련
						}
						
					}
				});
				rs.close();
				pstmt.close();

			}catch(Exception ex){
				ex.printStackTrace();
			}

			combo.setBounds(12, 20, 84, 26);
		}
		return combo;
	}
	private JFormattedTextField getWord() {
		if (word == null) {
			word = new JFormattedTextField((Format) null);
			word.addKeyListener(new KeyAdapter() {
				@Override
				public void keyReleased(KeyEvent arg0) {
					if(combo.getSelectedIndex() ==0){
						searchAll(); //전체조회
					}else{

						search(); //콤보박스 선택관련
					}
				}
			});
			word.setBounds(108, 20, 189, 28);
		}
		return word;
	}
	private JButton getSearch() {
		if (search == null) {
			search = new JButton("조회");
			search.setBackground(new Color(255, 250, 205));
			search.setFont(new Font("맑은 고딕", Font.BOLD, 12));
			search.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent arg0) {

					if(combo.getSelectedIndex() ==0){
						searchAll(); //전체조회
					}else{

						search(); //콤보박스 선택관련
					}
				}
			});
			search.setBounds(309, 20, 76, 26);
		}
		return search;
	}
	private JLayeredPane getLayeredPane_1_1() {
		if (layeredPane_1 == null) {
			layeredPane_1 = new JLayeredPane();
			layeredPane_1.setFont(new Font("맑은 고딕", Font.PLAIN, 12));
			layeredPane_1.setBorder(new TitledBorder(UIManager.getBorder("TitledBorder.border"), "\uC218\uC815 \uBC0F \uC0AD\uC81C", TitledBorder.LEADING, TitledBorder.TOP, null, new Color(0, 0, 0)));
			layeredPane_1.setBounds(432, 81, 276, 175);
			layeredPane_1.add(getLabel_1());
			layeredPane_1.add(getLabel_2());
			layeredPane_1.add(getMinor());
			layeredPane_1.add(getMajor());
			layeredPane_1.add(getModi());
			layeredPane_1.add(getDel());
		}
		return layeredPane_1;
	}
	private JLabel getLabel_1() {
		if (label_1 == null) {
			label_1 = new JLabel("대분류 명");
			label_1.setFont(new Font("맑은 고딕", Font.PLAIN, 12));
			label_1.setHorizontalAlignment(SwingConstants.RIGHT);
			label_1.setBounds(15, 42, 52, 15);
		}
		return label_1;
	}
	private JLabel getLabel_2() {
		if (label_2 == null) {
			label_2 = new JLabel("소분류 명");
			label_2.setFont(new Font("맑은 고딕", Font.PLAIN, 12));
			label_2.setHorizontalAlignment(SwingConstants.RIGHT);
			label_2.setBounds(15, 93, 52, 15);
		}
		return label_2;
	}
	private JFormattedTextField getMinor() {
		if (minor == null) {
			minor = new JFormattedTextField((Format) null);
			minor.setBounds(79, 87, 185, 28);
		}
		return minor;
	}
	private JFormattedTextField getMajor() {
		if (major == null) {
			major = new JFormattedTextField((Format) null);
			major.setBounds(79, 36, 185, 28);
		}
		return major;
	}
	private JButton getModi() {
		if (modi == null) {
			modi = new JButton("수정");
			modi.setBackground(new Color(255, 250, 205));
			modi.setFont(new Font("맑은 고딕", Font.BOLD, 12));
			modi.setForeground(new Color(0, 0, 0));
			modi.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent arg0) {
					modify();
				}
			});
			modi.setBounds(79, 139, 75, 23);
		}
		return modi;
	}
	private JButton getDel() {
		if (del == null) {
			del = new JButton("삭제");
			del.setBackground(new Color(255, 250, 205));
			del.setFont(new Font("맑은 고딕", Font.BOLD, 12));
			del.setForeground(Color.RED);
			del.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent arg0) {
					delete();
				}
			});
			del.setBounds(189, 139, 75, 23);
		}
		return del;
	}
	private JScrollPane getScrollPane_1() {
		if (scrollPane_1 == null) {
			scrollPane_1 = new JScrollPane();
			scrollPane_1.setBounds(432, 266, 276, 208);
			scrollPane_1.setViewportView(getTextArea());
		}
		return scrollPane_1;
	}
	private JTextArea getTextArea() {
		if (textArea == null) {
			textArea = new JTextArea();
			textArea.setBackground(new Color(255, 255, 224));
			textArea.setEditable(false);
			textArea.setForeground(Color.BLUE);
			textArea.setText(" * 코드조회 콤보박스에서 조회할 대분류명을 \r\n   선택 후 조회버튼을 클릭하세요\r\n\r\n - 검색창에 대분류, 소분류, 분류명 으로 \r\n   조회가 가능합니다.\r\n - 해당 데이터가 대분류, 소분류, 분류명 \r\n   순으로 검색이 됩니다.\r\n - 해당 데이터를 클릭시 수정 및 삭제 창에 \r\n   클릭한 데이터가 표시됩니다.\r\n - 수정 및 삭제 이용하여 데이터를 변경하세요.\r\n");
			textArea.setFont(new Font("맑은 고딕", Font.PLAIN, 12));
		}
		return textArea;
	}
	private JLabel getTtt() {
		if (ttt == null) {
			ttt = new JLabel("New label");

			Image img1 = Toolkit.getDefaultToolkit().getImage("tot.jpg");
			img1 = img1.getScaledInstance(270, 60, 0);
			ImageIcon icon1 = new ImageIcon(img1);
			ttt = new JLabel(icon1);
			ttt.setBounds(432, 10, 270, 60);

		}
		return ttt;
	}
}
